﻿using FySystem.Data.OrmA.Formatter;
using FySystem.Data.OrmA.Reflection;
using System;
using System.Collections.Generic;
using System.ComponentModel.Design;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json;

namespace FySystem.Data.OrmA.Resolvers.Lambdas
{
    class LambdaSQLCreater
    {
        /// <summary>
        /// 创建SQL语句
        /// </summary>
        /// <param name="dbSession"></param>
        /// <param name="isList"></param>
        /// <returns></returns>
        internal static FormatResult CreateSQL<T>(DBSession dbSession, ReflectionInfo reflectionInfo, List<DbSelectExpression<T>> opers, bool selectFlag, bool isList)
        {
            FormatResult res = new FormatResult();
            try
            {
                StringBuilder sqlSelect = new StringBuilder();
                StringBuilder sqlWhere = new StringBuilder();
                List<DbParameter> parameters = new List<DbParameter>();
                ISqlFormatter sqlFormatter = dbSession.DBConfig.SQLFormatter;

                bool isSelectFirst = true;
                bool isWhereFirst = true;
                bool isGroupbyFirst = true;
                bool isOrderbyFirst = true;
                foreach(var oper in opers)
                {
                    switch (oper.DbKeywords)
                    {
                        case DbKeywords.SELECT:
                            CreateSelectSQL(sqlFormatter, reflectionInfo, oper, selectFlag, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.MAX:
                            LinkExpressionFields(sqlFormatter, oper, "MAX(", ")", true, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.MIN:
                            LinkExpressionFields(sqlFormatter, oper, "MIN(", ")", true, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.AVG:
                            LinkExpressionFields(sqlFormatter, oper, "AVG(", ")", true, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.SUM:
                            LinkExpressionFields(sqlFormatter, oper, "SUM(", ")", true, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.COUNT:
                            if (isSelectFirst == false)
                                sqlSelect.Append(',');
                            sqlSelect.Append("COUNT(1)");
                            break;
                        case DbKeywords.NVL:
                            NvlSQL(sqlFormatter, oper, ref sqlSelect, ref isSelectFirst);
                            break;
                        case DbKeywords.WHERE:
                            CreateWhereSQL(dbSession.DBConfig.DbProviderFactory, sqlFormatter, reflectionInfo, oper, ref sqlWhere, ref parameters, ref isWhereFirst);
                            break;
                        case DbKeywords.GROUPBY:
                            GroupBySQL(sqlFormatter, oper, ref sqlWhere, ref isGroupbyFirst);
                            break;
                        case DbKeywords.ORDERBY:
                            OrderBySQL(sqlFormatter, oper, ref sqlWhere, ref isOrderbyFirst);
                            break;
                        case DbKeywords.LIMIT:
                            LimitSQL(sqlFormatter, oper, ref sqlWhere);
                            break;
                    }
                }

                string schema = string.Empty;
                if (!string.IsNullOrWhiteSpace(reflectionInfo.Table.Schema))
                    schema = $"{reflectionInfo.Table.Schema}.";
                res.Sql = sqlSelect.Append($" FROM {schema}{reflectionInfo.Table.Table}").Append(sqlWhere).ToString();
                if (parameters.Count > 0)
                    res.Parameters = parameters.ToArray();
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        private static void LimitSQL<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, ref StringBuilder sql)
        {
            try
            {

            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void OrderBySQL<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, ref StringBuilder sql, ref bool isOrderbyFirst)
        {
            try
            {
                if (isOrderbyFirst)
                    sql.Append(" ORDER BY ");

                bool isAsc = (bool)oper.Parameters[0];
                string right = isAsc ? " ASC" : " DESC";

                LinkExpressionFields(sqlFormatter, oper, "", right, false, ref sql, ref isOrderbyFirst);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void GroupBySQL<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, ref StringBuilder sql, ref bool isGroupbyFirst)
        {
            try
            {
                if (isGroupbyFirst)
                    sql.Append(" GROUP BY ");

                LinkExpressionFields(sqlFormatter, oper, ref sql, ref isGroupbyFirst);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void CreateWhereSQL<T>(DbProviderFactory dbProviderFactory, ISqlFormatter sqlFormatter, ReflectionInfo reflectionInfo, 
            DbSelectExpression<T> oper, ref StringBuilder sql, ref List<DbParameter> parameters, ref bool isWhereFirst)
        {
            try
            {
                if (isWhereFirst)
                    sql.Append(" WHERE ");
                else
                    sql.Append(" AND ");

                BinaryExpression body = (BinaryExpression)oper.ExpOper.Body;
                MemberExpression left = (MemberExpression)body.Left;
                object value = null;

                if (body.Right.NodeType == ExpressionType.Constant)
                {
                    //如果传入的是值
                    value = ((ConstantExpression)body.Right).Value;
                }
                else if (body.Right.NodeType == ExpressionType.MemberAccess)
                {
                    //如果传入的是变量
                    value = Expression.Lambda(body.Right).Compile().DynamicInvoke();
                }
                else if (body.Right.NodeType == ExpressionType.Convert)
                {
                    value = Expression.Lambda(body.Right).Compile().DynamicInvoke();
                }
                else if (body.Right.NodeType == ExpressionType.Call)
                {
                    value = Expression.Lambda(body.Right).Compile().DynamicInvoke();
                }

                sql.Append(left.Member.Name);
                sql.Append(ConvertOperator(body.NodeType));
                var param = BaseFormatter.CreateSQLParameter(sqlFormatter, dbProviderFactory, Attributes.DbFieldTypes.Default, parameters.Count, value);
                sql.Append(param.Item1.ParameterName);
                parameters.Add(param.Item1);

                isWhereFirst = false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void NvlSQL<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, ref StringBuilder sql, ref bool isSelectFirst)
        {
            try
            {
                if (oper.ExpSelect.Body.NodeType == ExpressionType.New)
                {
                    NewExpression body = (NewExpression)oper.ExpSelect.Body;
                    object[] value = oper.Parameters;
                    foreach (var item in body.Members)
                    {
                        if (!isSelectFirst)
                            sql.Append(',');
                        sql.Append(sqlFormatter.GenNvlSQL(item.Name, value)).Append($" AS {sqlFormatter.FormatFields(item.Name)}");
                        isSelectFirst = false;
                    }
                }
                else if (oper.ExpSelect.Body.NodeType == ExpressionType.MemberAccess)
                {
                    MemberExpression body = (MemberExpression)oper.ExpSelect.Body;
                    object[] value = oper.Parameters;
                    if (!isSelectFirst)
                        sql.Append(',');
                    sql.Append(sqlFormatter.GenNvlSQL(body.Member.Name, value)).Append($" AS {sqlFormatter.FormatFields(body.Member.Name)}");
                    isSelectFirst = false;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static void CreateSelectSQL<T>(ISqlFormatter sqlFormatter, ReflectionInfo reflectionInfo, DbSelectExpression<T> oper, bool selectFlag, ref StringBuilder sql, ref bool isSelectFirst)
        {
            try
            {
                sql.Append("SELECT ");
                if (oper.ExpSelect == null)
                {
                    //如果没有传入数据库字段
                    if (selectFlag == false)
                    {
                        sql.Append(BaseFormatter.CreateSelectPartSQL(sqlFormatter, reflectionInfo, true, false, false));
                        isSelectFirst = false;
                    }
                }
                else
                {
                    LinkExpressionFields(sqlFormatter, oper, ref sql, ref isSelectFirst);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 拼接x=>new {x.id,x.name}这样的字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFormatter"></param>
        /// <param name="opers"></param>
        /// <param name="sql"></param>
        /// <param name="isFirst"></param>
        private static void LinkExpressionFields<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, ref StringBuilder sql, ref bool isFirst)
        {
            try
            {
                if (oper.ExpSelect.Body.NodeType == ExpressionType.New)
                {
                    NewExpression body = (NewExpression)oper.ExpSelect.Body;
                    foreach (var member in body.Members)
                    {
                        if (!isFirst)
                            sql.Append(',');
                        sql.Append(sqlFormatter.FormatFields(member.Name));
                        isFirst = false;
                    }
                }
                else if (oper.ExpSelect.Body.NodeType == ExpressionType.MemberAccess)
                {
                    MemberExpression body = (MemberExpression)oper.ExpSelect.Body;
                    if (!isFirst)
                        sql.Append(',');
                    sql.Append(sqlFormatter.FormatFields(body.Member.Name));
                    isFirst = false;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 拼接x=>new {x.id,x.name}这样的字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFormatter"></param>
        /// <param name="opers"></param>
        /// <param name="sql"></param>
        /// <param name="isFirst"></param>
        private static void LinkExpressionFields<T>(ISqlFormatter sqlFormatter, DbSelectExpression<T> oper, string left,string right, bool appendAS, ref StringBuilder sql, ref bool isFirst)
        {
            try
            {
                if (oper.ExpSelect.Body.NodeType == ExpressionType.New)
                {
                    NewExpression body = (NewExpression)oper.ExpSelect.Body;
                    foreach (var member in body.Members)
                    {
                        if (!isFirst)
                            sql.Append(',');
                        sql.Append(left).Append(sqlFormatter.FormatFields(member.Name)).Append(right);
                        if(appendAS)
                            sql.Append($" AS {sqlFormatter.FormatFields(member.Name)}");
                        isFirst = false;
                    }
                }
                else if (oper.ExpSelect.Body.NodeType == ExpressionType.MemberAccess)
                {
                    MemberExpression body = (MemberExpression)oper.ExpSelect.Body;
                    if (!isFirst)
                        sql.Append(',');
                    sql.Append(left).Append(sqlFormatter.FormatFields(body.Member.Name)).Append(right);
                    if(appendAS)
                        sql.Append($" AS {sqlFormatter.FormatFields(body.Member.Name)}");
                    isFirst = false;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 转换操作符
        /// </summary>
        /// <param name="nodeType"></param>
        /// <returns></returns>
        private static string ConvertOperator(ExpressionType nodeType)
        {
            string res = string.Empty;
            switch (nodeType)
            {
                case ExpressionType.Equal:
                    res = " = ";
                    break;
                case ExpressionType.GreaterThan:
                    res = " > ";
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    res = " >= ";
                    break;
                case ExpressionType.LessThan:
                    res = " < ";
                    break;
                case ExpressionType.LessThanOrEqual:
                    res = " <= ";
                    break;
                case ExpressionType.NotEqual:
                    res = " <> ";
                    break;
                default:
                    throw new Exception($"不支持的操作符:{nodeType}");
            }
            return res;
        }

    }
}
